<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;

class ToolController extends Controller
{
    //导出工器具类别及检测项目
    public function exportToolItem(){
        $items = DB::table('a_std_tool_item_rel as a')
            ->leftJoin('a_standard as b','a.std_id','=','b.id')
            ->leftJoin('a_tool as c','a.tool_id','=','c.id')
            ->leftJoin('a_testitem as d','a.testitem_id','=','d.id')
            ->where([
                    'a.ishide'=>0,
                    'b.ishide'=>0,
                    'c.ishide'=>0,
                    'd.ishide'=>0,
                ])
            ->select('c.name as tool_name','b.num as std_num','d.name as item_name')
            ->orderBy('a.sort','asc')
            ->get();
        $data = [];
        foreach ($items as $item) {
            $toolName = $item->tool_name;
            $stdNum = $item->std_num;
            if($toolName && $stdNum && !is_numeric(mb_substr($toolName,-1))){
                $data[$toolName][$stdNum][] = $item->item_name;
            }
        }
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $title = '安全工器具类别及检测项目';
        $sheet->setTitle($title);
        $sheet->mergeCells('A1:C1')->setCellValue('A1',$title);
        $sheet->getStyle('A1')->getAlignment()->setHorizontal('center'); //标题行
        $sheet->setCellValue('A2','工器具');
        $sheet->setCellValue('B2','标准');
        $sheet->setCellValue('C2','项目');
        $startIndex = 3;
        foreach ($data as $toolName => $item) {
            $endIndex = $startIndex + count($item) - 1;
            // dump("{$toolName}:{$startIndex}-{$endIndex}");
            $sheet->mergeCells("A{$startIndex}:A{$endIndex}");
            $sheet->setCellValue("A{$startIndex}",$toolName);
            foreach($item as $stdNum => $itemNames){
                $endIndex = $startIndex ;
                // dump("{$stdNum}:{$startIndex}-{$endIndex}");
                $sheet->mergeCells("B{$startIndex}:B{$endIndex}");
                $sheet->setCellValue("B{$startIndex}",$stdNum);
                $sheet->setCellValue("C{$startIndex}",implode('，',$itemNames));
                $startIndex += 1;
            }
        }
        $sheet->getColumnDimension('A')->setWidth('30');
        $sheet->getColumnDimension('B')->setWidth('20');
        $sheet->getColumnDimension('C')->setWidth('100');
        $sheet->getStyle('A:C')->getAlignment()->applyFromArray(
                  [
                      // 'horizontal'   => 'center',
                      'vertical'   => 'center',
                      'textRotation' => 0,
                      'wrapText'     => TRUE,
                      'shrinkToFit'     => TRUE
                  ]
            );
        // dd($startIndex,$endIndex);
        $file_name = "{$title}.xls"; //输出的文件名称
        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出Excel 07文件
        header('Content-Type: application/vnd.ms-excel'); //告诉浏览器输出Excel 03版本文件
        header('Content-Disposition: attachment;filename=' . urlencode($file_name)); //告诉浏览器输出的文件名称
        header('Cache-Control: max-age=0'); //禁止缓存
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    }
    //样品类型展示
    public function index(Request $request)
    {
        if ($request->expectsJson()) {
            // dd($request->all());
            // sleep(1);
            $data = DB::table('a_tool as a')->leftJoin('a_tool_class as b','a.tool_class_id','=','b.id')->select('a.*','b.name as class_name')->get();
            $jsonData['data'] = $data;
            return response()->json($jsonData);
        } else {
            // $data = Manager::get();
            return view('admin.tool.index');
        }
    }

    //样品类型 添加
    public function add(Request $request)
    {
        if ($request->isMethod('POST')) {
            $post_data = $request->only(['name', 'unit']);
            try {
                //插入数据    insert 返回true 或 false
                $result = DB::table('a_tool')->insert($post_data);
            } catch (\Throwable $e) {
                return '2';
            }
            //由于框架自身不支持响应bool值，所以转化一种形式
            return $result ? '1' : '0';
        } else {
            return view('admin.tool.add');
        }
    }

    //样品类型 修改
    public function edit(Request $request)
    {
        $id = $request->input('id');

        if ($request->isMethod('POST')) {
            $data = $request->only(['ishide', 'unit','en','tool_class_id']);

            try {
                //update 返回受影响的行数
                $data['en'] = strtoupper($data['en']);
                $result = DB::table('a_tool')->where('id', $id)->update($data);
            } catch (\Throwable $e) {
                return '2';
            }

            //由于框架自身不支持响应bool值，所以转化一种形式
            return $result >= 0 ? '1' : '0';
        } else {
            //查询信息
            $info_tool = DB::table('a_tool as a')->leftJoin('a_tool_class as b','a.tool_class_id','=','b.id')
                ->select('a.*','b.name as class_name')
                ->where('a.id',$id)->first();
            $classes = DB::table('a_tool_class')->get();
            return view('admin.tool.edit', compact('info_tool','classes'));
        }
    }

    //样品类型 删除
    public function del(Request $request)
    {

        $id = $request->input('id');
        try {
            // delete 返回受影响的行数
            $result = DB::table('a_tool')->where('id', $id)->delete();
        } catch (\Throwable $e) {
            return '2';
        }
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $result >= 0 ? '1' : '0';
    }

    //关联标准
    public function relStd(Request $request)
    {
        //chu增加put方法
        if ($request->isMethod('PUT')) {
            $sorts = array_filter($request->input('sort',[]));
            if(!$sorts){
                return '请先修改排序！';
            }
            DB::beginTransaction();
            try {
                foreach($sorts as $id =>$item){
                    DB::table('a_std_tool_rel')->where('id',$id)->update(['sort'=>$item]);
                }
            } catch(\Exception $e) {
                DB::rollBack();
                return $e->getMessage();
            }
            DB::commit();
            return 1;
        }
        $id = $request->id; //工器具ID
        if ($request->isMethod('POST')) {
            $data = $request->only(['std_id', 'remark', 'cycle']);
            $data['tool_id'] = $id;
            // dd($data);
            try {
                //插入数据    insert 返回true 或 false
                $result = DB::table('a_std_tool_rel')->insert($data);
            } catch (\Throwable $e) {
                return '2';
            }
            //由于框架自身不支持响应bool值，所以转化一种形式 Exception
            return $result ? '1' : '0';
        } else {

            $data_std = DB::table('a_standard')->get();
            $data_tool = DB::table('a_tool')->find($id);

            $sql = "SELECT
            a_std_tool_rel.id,
            a_standard.num,
            a_std_tool_rel.cycle,
            a_std_tool_rel.remark,
            a_std_tool_rel.sort,
            a_std_tool_rel.ctime
        FROM
            a_std_tool_rel
        LEFT JOIN a_standard ON a_std_tool_rel.std_id = a_standard.id
        WHERE
            a_std_tool_rel.tool_id = :id";

            $data_rel = DB::select($sql,['id'=>$id]);
            return view('admin.tool.relStd', compact('data_std', 'data_tool', 'data_rel'));
        }
    }

    //关联参数（额定电压、分类）
    public function relParam(Request $request)
    {
        $std_tool_rel_id = $request->id; //标准与工器具关联 ID

        if ($request->isMethod('POST')) {
            $data = $request->only(['std_id', 'tool_id', 'type', 'val','cycle']);
            $data['std_tool_rel_id'] = $std_tool_rel_id;
            // dd($data);
            try {
                //插入数据    insert 返回true 或 false
                $result = DB::table('a_voltage_class')->insertGetId($data);
            } catch (\Throwable $e) {
                return '2';
            }
            //将排序字段更新为id
            DB::table('a_voltage_class')->where('id', $result)->update(['sort' => $result]);
            //由于框架自身不支持响应bool值，所以转化一种形式 Exception
            return $result ? '1' : '0';
        } else {

            $sql = "SELECT
                        A.id,
                        A.std_id,
                        A.tool_id,
                        B.num,
                        C.`name`,
                        A.cycle
                    FROM
                        a_std_tool_rel A
                    LEFT JOIN a_standard B ON A.std_id = B.id
                    LEFT JOIN a_tool C ON A.tool_id = C.id
                    WHERE
                        A.id = :id";
            $info_data = DB::select($sql, ['id' => $std_tool_rel_id]);
            $info_data = $info_data[0];
            $data_rel = DB::table('a_voltage_class')->where('std_tool_rel_id', $std_tool_rel_id)->orderBy('type')->orderBy('sort')->get();
            return view('admin.tool.relParam', compact('info_data', 'data_rel'));
        }
    }

    //修改 关联参数（额定电压、分类）
    public function editParam(Request $request)
    {
        $vol_class_id = $request->input('id');

        if ($request->isMethod('POST')) {
            $data = $request->only(['ishide', 'sort','cycle']);
            try {
                //update 返回受影响的行数
                $result = DB::table('a_voltage_class')->where('id', $vol_class_id)->update($data);
            } catch (\Throwable $e) {
                return '2';
            }

            //由于框架自身不支持响应bool值，所以转化一种形式
            return $result >= 0 ? '1' : '0';
        } else {
            //查询信息
            $vol_class_info = DB::table('a_voltage_class')->find($vol_class_id);
            $data_tool = DB::table('a_tool')->find($vol_class_info->tool_id);

            return view('admin.tool.editParam', compact('vol_class_info', 'data_tool'));
        }
    }

    //关联标准 删除
    public function delRelStd(Request $request)
    {

        $id = $request->id;

        //  delete 返回受影响的行数
        $result = DB::table('a_std_tool_rel')->where('id', $id)->delete();
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $result >= 0 ? '1' : '0';
    }

    //关联参数（额定电压、分类） 删除
    public function delParam(Request $request)
    {
        $id = $request->id;

        try {
            //  delete 返回受影响的行数
            $result = DB::table('a_voltage_class')->where('id', $id)->delete();
        } catch (\Throwable $e) {
            return '2';
        }
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $result >= 0 ? '1' : '0';
    }

    /**
     * 排序 参数（额定电压、分类）
     */
    public function saveSortRelParam(Request $request)
    {
        $arr = $request->sort;
        if ($arr) {
            # 如果$arr 不为null 就遍历，遍历null会报错
            $resnum = 0;
            foreach ($arr as $key => $val) {
                $val = intval($val);
                $result = DB::table('a_voltage_class')->where('id', $key)->update(['sort' => $val]);
                $result ? $resnum+=1 : $resnum;
            }
            return response('您修改了'.$resnum.'条数据',200);
        }
    }
}
